let mysql = require('mysql');

// 创建连接池，效率更高，不需要每次操作数据库都创建连接
let pool=mysql.createPool({
    host:'localhost',
    user:'root',
    password:'123456',
    database:'score',
    port:'3306',
    connectionLimit:50,//允许连接数
    multipleStatements : true,  //是否允许执行多条sql语句
    timezone:"08:00" //大坑，必须加这一句，否则时间不对劲
})

//封装数据库sql请求操作，返回的是一个包含对象的数组
let query = ( sql , ...params )=>{
     return new Promise(function(resolve,reject){
         //从连接池中拿一条链接
         pool.getConnection(function(err,connection){
            if(err){
                return reject(err);
            }
            connection.query( sql , ...params , function(error,res){
                // console.log(res);
                connection.release();
                if(error){
                    return  reject(error);
                }
                resolve(res);
            });
        });
    });
 };

const sql = {
    // 班级
    class:{
        list:'SELECT * FROM class',
        add:'INSERT INTO class (class_id,class_name,createTime) VALUES (?,?,now())'
    },
    // 学生
    student:{
        list:'SELECT a.*, b.class_name FROM students a,class b WHERE a.class_id = b.class_id', //无条件
        list_classId:'SELECT a.*, b.class_name FROM students a,class b WHERE a.class_id = b.class_id AND a.class_id = ?', //根据班级查
        add:'INSERT INTO students (student_id,student_name,sex,class_id) VALUES (?,?,?,?)'
    }
};

module.exports = {
    query,
    sql
};